package org.tech4j.edi.persistence.jdbc;

import com.google.common.collect.Maps;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.tech4j.common.core.model.page.PageQuery;
import org.tech4j.common.core.model.page.PageResult;
import org.tech4j.common.utils.EnumUtils;
import org.tech4j.edi.common.entity.RetryBizRecord;
import org.tech4j.edi.common.enums.RetryStatusEnum;
import org.tech4j.edi.common.result.EdiResult;
import org.tech4j.edi.persistence.api.RetryRecordPersistService;
import org.tech4j.edi.persistence.api.query.RetryCountQuery;
import org.tech4j.edi.persistence.api.query.RetryRecordQuery;
import org.tech4j.edi.persistence.api.query.RetryRecordScanQuery;
import org.tech4j.edi.persistence.jdbc.util.DefaultJdbcListFactory;
import org.tech4j.edi.persistence.jdbc.util.JdbcListFactory;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;

/**
 * @program: tech4j
 * @description:
 * @author: YuKai Fan
 * @create: 2025/3/8 09:10
 **/
public class JdbcRetryRecordPersistService implements RetryRecordPersistService {
    private static final Logger log = LoggerFactory.getLogger(JdbcRetryRecordPersistService.class);

    private static final String RETRY_RECORD_FIELDS = "biz_no, biz_id, biz_type, biz_source, biz_params, ext, env, status, retry_times, error_message, sharding_key, last_op_time, created_time, modified_time, remark, version";

    private static final String BASE_FIND_STATEMENT = "select id, " + RETRY_RECORD_FIELDS
            + " from retry_biz_record";

    private static final String DEFAULT_SELECT_SQL = BASE_FIND_STATEMENT + " where id = ? and biz_type = ?";
    private static final String DEFAULT_INSERT_STATEMENT = "insert into retry_biz_record(" + RETRY_RECORD_FIELDS
            + ", id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

    private static final String DEFAULT_UPDATE_STATUS = "update retry_biz_record set status = ? where id = ? and biz_type = ?";
    private static final String DEFAULT_UPDATE_STATUS_AND_REMARK = "update retry_biz_record set status = ? and remark = ? where id = ? and biz_type = ?";
    private static final String DEFAULT_SELECT_RETRY_MAX_ID_PREFIX = "select max(id) from retry_biz_record where status in (3, 0) and env = ?";
    private static final String DEFAULT_SELECT_RETRY_MIN_ID_PREFIX = "select min(id) from retry_biz_record where status in (3, 0) and env = ?";
    private static final String DEFAULT_SELECT_RETRY_COUNT = "select count(*) from retry_biz_record where env = ? ";

    private static final String DEFAULT_SCAN_RETRY_RECORD = BASE_FIND_STATEMENT + " where status in (3, 0) and env = ? ";

    private String insertRetryRecordSql = DEFAULT_INSERT_STATEMENT;

    private String selectRetryRecordSql = DEFAULT_SELECT_SQL;

    private String updateStatusSql = DEFAULT_UPDATE_STATUS;
    private String updateStatusAndRemarkSql = DEFAULT_UPDATE_STATUS_AND_REMARK;

    private final RowMapper<RetryBizRecord> rowMapper = new RetryBizRecordRowMapper();
    private final JdbcTemplate jdbcTemplate;

    private final JdbcListFactory listFactory;

    public JdbcRetryRecordPersistService(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.listFactory = new DefaultJdbcListFactory(new NamedParameterJdbcTemplate(jdbcTemplate));
    }

    @Override
    public EdiResult<String> persistRetryRecord(RetryBizRecord retryBizRecord) {
        Date now = new Date();
        int update = jdbcTemplate.update(insertRetryRecordSql
                , retryBizRecord.getBizNo()
                , retryBizRecord.getBizId()
                , retryBizRecord.getBizType()
                , retryBizRecord.getBizSource()
                , retryBizRecord.getBizParams()
                , retryBizRecord.getExt()
                , retryBizRecord.getEnv()
                , retryBizRecord.getRecordStatus().getType()
                , retryBizRecord.getRetryTimes()
                , retryBizRecord.getErrorMessage()
                , retryBizRecord.getShardingKey()
                , retryBizRecord.getLastOpTime()
                , now
                , now
                , retryBizRecord.getRemark()
                , retryBizRecord.getVersion()
                , retryBizRecord.getId()
        );
        if (update <= 0) {
            return EdiResult.ofFailed0(500, "新增重试记录失败");
        }
        return EdiResult.ofSuccess(retryBizRecord.getId());
    }

    @Override
    public RetryBizRecord getRetryBizRecordById(String id, String bizType) {
        return jdbcTemplate.queryForObject(selectRetryRecordSql, new RetryBizRecordRowMapper(), id, bizType);
    }

    @Override
    public EdiResult<Void> updateRetryStatus(RetryBizRecord retryBizRecord, RetryStatusEnum status) {
        int update = jdbcTemplate.update(updateStatusSql, status.getType(), retryBizRecord.getId(), retryBizRecord.getBizType());
        if (update != 1) {
            return EdiResult.ofFailed0(500, "更新重试记录状态失败");
        }
        return EdiResult.ofSuccess();
    }

    @Override
    public EdiResult<Void> updateRetryStatusAndRemark(RetryBizRecord retryBizRecord, RetryStatusEnum status, String remark) {
        int update = jdbcTemplate.update(updateStatusAndRemarkSql, status.getType(), remark, retryBizRecord.getId(), retryBizRecord.getBizType());
        if (update != 1) {
            return EdiResult.ofFailed0(500, "更新重试记录状态失败");
        }
        return EdiResult.ofSuccess();
    }

    @Override
    public String getRetryMinId(String env, LocalDateTime startTime, LocalDateTime endTime) {
        String sql = DEFAULT_SELECT_RETRY_MIN_ID_PREFIX;
        if (Objects.nonNull(startTime) && Objects.nonNull(endTime)) {
            sql = sql + " and created_time >= ? and created_time <= ?";
        }
        return jdbcTemplate.queryForObject(sql, String.class, env, startTime, endTime);
    }

    @Override
    public String getRetryMaxId(String env, LocalDateTime startTime, LocalDateTime endTime) {
        String sql = DEFAULT_SELECT_RETRY_MAX_ID_PREFIX;
        if (Objects.nonNull(startTime) && Objects.nonNull(endTime)) {
            sql = sql + " and created_time >= ? and created_time <= ?";
        }
        return jdbcTemplate.queryForObject(sql, String.class, env, startTime, endTime);
    }

    @Override
    public Long count(RetryRecordQuery query) {
        StringBuilder stringBuilder = new StringBuilder(DEFAULT_SELECT_RETRY_COUNT);
        Map<String, Object> parameters = buildListParamAndSql(null, query, stringBuilder);
        return listFactory.count(stringBuilder.toString(), parameters);
    }

    @Override
    public List<RetryBizRecord> list(RetryRecordQuery query) {
        StringBuilder stringBuilder = new StringBuilder(BASE_FIND_STATEMENT);
        Map<String, Object> parameters = buildListParamAndSql(null, query, stringBuilder);
        return listFactory.getList(stringBuilder.toString(), parameters, rowMapper);
    }

    @Override
    public PageResult<RetryBizRecord> page(PageQuery page, RetryRecordQuery query) {
        StringBuilder stringBuilder = new StringBuilder(BASE_FIND_STATEMENT);
        Map<String, Object> parameters = buildListParamAndSql(page, query, stringBuilder);
        long count = 0;
        if (Boolean.TRUE.equals(page.getCount())) {
            count = Optional.ofNullable(this.count(query))
                    .orElse(0L);
        }
        List<RetryBizRecord> retryBizRecordList = listFactory.getList(stringBuilder.toString(), parameters, rowMapper);
        if (CollectionUtils.isNotEmpty(retryBizRecordList)) {
            return PageResult.emptyPageResult(page);
        }
        PageResult<RetryBizRecord> pageResult = new PageResult<>();
        pageResult.setPageNum(page.getPageNum());
        pageResult.setPageSize(page.getPageSize());
        pageResult.setRows(retryBizRecordList);
        pageResult.setTotal(count);
        return pageResult;
    }

    @Override
    public List<RetryBizRecord> scanRetryBizRecord(RetryRecordScanQuery query) {
        StringBuilder stringBuilder = new StringBuilder(DEFAULT_SCAN_RETRY_RECORD);
        Map<String, Object> parameters = Maps.newHashMap();
        if (query.getMinId() != null && query.getMaxId() != null) {
            stringBuilder.append(" and id > :minId and id <= :maxId");
            parameters.put("minId", query.getMinId());
            parameters.put("maxId", query.getMaxId());
        }
        if (query.getStartTime() != null && query.getEndTime() != null) {
            stringBuilder.append(" and created_time >= :startTime and created_time <= :endTime");
            parameters.put("startTime", query.getStartTime());
            parameters.put("endTime", query.getEndTime());
        }
        stringBuilder.append(" order by id")
                .append(" limit :maxQuerySize");
        parameters.put("maxQuerySize", query.getMaxQuerySize());
        return listFactory.getList(stringBuilder.toString(), parameters, rowMapper);
    }

    private static Map<String, Object> buildListParamAndSql(PageQuery page, RetryRecordQuery query, StringBuilder stringBuilder) {
        Map<String, Object> parameters = Maps.newHashMap();
        stringBuilder.append(" where 1=1");
        if (CollectionUtils.isNotEmpty(query.getIdList())) {
            stringBuilder.append(" and id in (:idList)");
            parameters.put("idList", query.getIdList());
        }
        if (StringUtils.isNotBlank(query.getBizType())) {
            stringBuilder.append(" and biz_type = :bizType");
            parameters.put("bizType", query.getBizType());
        }
        if (Objects.nonNull(query.getRetryStatus())) {
            stringBuilder.append(" and status = :status");
            parameters.put("status", query.getRetryStatus().getType());
        }
        if (CollectionUtils.isNotEmpty(query.getBizNoList())) {
            stringBuilder.append(" and biz_no in (:bizNoList)");
            parameters.put("bizNoList", query.getBizNoList());
        }
        if (Objects.nonNull(query.getStartTime())) {
            stringBuilder.append(" and created_time >= :startTime");
            parameters.put("startTime", query.getStartTime());
        }
        if (StringUtils.isNotBlank(query.getShardingKey())) {
            stringBuilder.append(" and sharding_key = :shardingKey");
            parameters.put("shardingKey", query.getShardingKey());
        }
        if (page != null) {
            stringBuilder.append(" limit :offset, :limit");
            parameters.put("offset", (page.getPageNum() - 1) * page.getPageSize());
            parameters.put("limit", page.getPageSize());
        }
        return parameters;
    }


    private static class RetryBizRecordRowMapper implements RowMapper<RetryBizRecord> {

        @Override
        public RetryBizRecord mapRow(ResultSet rs, int rowNum) throws SQLException {
            RetryBizRecord retryBizRecord = new RetryBizRecord();
            int status = rs.getInt(9);
            retryBizRecord.setId(rs.getString(1));
            retryBizRecord.setBizNo(rs.getString(2));
            retryBizRecord.setBizId(rs.getString(3));
            retryBizRecord.setBizType(rs.getString(4));
            retryBizRecord.setBizSource(rs.getString(5));
            retryBizRecord.setBizParams(rs.getString(6));
            retryBizRecord.setExt(rs.getString(7));
            retryBizRecord.setEnv(rs.getString(8));
            retryBizRecord.setRecordStatus(EnumUtils.getBy(RetryStatusEnum.class, (statusEnum -> statusEnum.getType() == status)));
            retryBizRecord.setRetryTimes(rs.getInt(10));
            retryBizRecord.setErrorMessage(rs.getString(11));
            retryBizRecord.setShardingKey(rs.getString(12));
            retryBizRecord.setLastOpTime(rs.getDate(13));
            retryBizRecord.setCreatedTime(rs.getDate(14));
            retryBizRecord.setModifiedTime(rs.getDate(15));
            retryBizRecord.setRemark(rs.getString(16));
            retryBizRecord.setVersion(rs.getInt(17));

            return retryBizRecord;
        }
    }
}
